package com.mlnx.work.utils;

import com.mlnx.work.enums.ResponseEnum;
import com.mlnx.work.exception.WorkServiceException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.*;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: Akrio
 * @Date: 2021-01-26 15:13
 */
public class ExcelFileUtil {
    public static List<Map<String, String>> analysisExcel(MultipartFile file){
//        String columns[] = {"name","age","score"};
        Workbook wb = null;
        String fileName = file.getOriginalFilename();
        String extString = fileName.substring(fileName.lastIndexOf("."));
        Sheet sheet = null;
        List<Map<String,String>> list = null;
        Row row = null;
        String cellData = null;
        InputStream is = null;
        try {
            is = file.getInputStream();
            if(".xls".equals(extString)){
                wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(extString)){
                wb = new XSSFWorkbook(is);
            }else {
                throw new WorkServiceException(ResponseEnum.MYSQL_OPERATION_EXCEPTION);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        if(wb != null){
            //用来存放表中数据
            list = new ArrayList<Map<String,String>>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            //获取第一行
            row = sheet.getRow(0);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            String[] columns = new String[colnum];
            for (int i=0;i<colnum;i++){
                cellData = stringFormat((String) getCellFormatValue(row.getCell(i),true));
                columns[i] = cellData;
            }
            for (int i = 1; i<rownum; i++) {
                Map<String,String> map = new LinkedHashMap<String,String>();
                row = sheet.getRow(i);
                if(row !=null){
                    for (int j=0;j<colnum;j++){

//                    if("类别".equals(tatle[j])){
//                        if("任务".equals(o.toString())){
//                            o=1;
//                        }else if("子任务".equals(o.toString())){
//                            o=2;
//                        }else {
//                            o=0;
//                        }
//                    }
                        boolean flag= j==0;

                        cellData = (String) getCellFormatValue(row.getCell(j),flag);
                        map.put(columns[j], cellData);
                    }
                }else{
                    break;
                }
                list.add(map);
            }
        }
        return list;
    }
    public static <T> byte[] createExcel(List<T> list, String[] tatle){
        byte[] bytes = new byte[0];
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("sheet1");//表名

        HSSFCellStyle style = wb.createCellStyle();
        Font font=wb.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setFontName("Arial");
        style.setFont(font);
        HSSFPatriarch patriarch=sheet.createDrawingPatriarch();
        HSSFRow row = sheet.createRow(0);//第一行
        for(int i=0;i<tatle.length;i++){
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(tatle[i]);//成员名称
            cell.setCellStyle(style);
            sheet.setColumnWidth(i,(tatle[i].length()+4) * 512);        //设置宽
        }
        try {
//            BufferedImage bleak = ImageIO.read(new File("color/blue.png"));
//            BufferedImage bleak = ImageIO.read(new File("color/green.png"));
//            BufferedImage bleak = ImageIO.read(new File("color/purple.png"));
//            BufferedImage bleak = ImageIO.read(new File("color/red.png"));
//            BufferedImage bleak = ImageIO.read(new File("color/yellow.png"));
            for (int i = 0; i < list.size(); i++) {//第二行开始的N行
                HSSFRow rows = sheet.createRow(i + 1);
                rows.setHeight((short)400);                         //设置高度
                Field[] fields = list.get(i).getClass().getDeclaredFields();
                for (int j = 0; j < tatle.length; j++) {
                    if (!fields[j].isAccessible())
                        fields[j].setAccessible(true);
                    HSSFCell cell = rows.createCell(j);   //创建单元格
                    cell.setCellStyle(style);
                    Object o = fields[j].get(list.get(i));
                    if (o == null) {
                        cell.setCellValue("");
                    } else {
                        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
                        BufferedImage color = null;
                        if("已逾期".equals(o.toString())){
                            color = ImageIO.read(new File("work_web/target/classes/static/color/red.png"));
                        }else if("移交中".equals(o.toString())){
                            color = ImageIO.read(new File("work_web/target/classes/static/color/purple.png"));
                        }else if("进行中".equals(o.toString())){
                            color = ImageIO.read(new File("work_web/target/classes/static/color/green.png"));
                        }else if("风险项".equals(o.toString())){
                            color = ImageIO.read(new File("work_web/target/classes/static/color/yellow.png"));
                        }else if("完成待确认".equals(o.toString())){
                            color = ImageIO.read(new File("work_web/target/classes/static/color/blue.png"));
                        }else if("完成".equals(o.toString())){
                            color = ImageIO.read(new File("work_web/target/classes/static/color/bleak.png"));
                        }else {
                            cell.setCellValue(o.toString());
                        }
                        if(color!=null){
                            ImageIO.write(color,"png",byteArrayOutputStream);
                            HSSFClientAnchor anchor = new HSSFClientAnchor(300,0,300*2,255 ,(short)j,i+1,(short)j,i+1);
                            patriarch.createPicture(anchor,wb.addPicture(byteArrayOutputStream.toByteArray(),HSSFWorkbook.PICTURE_TYPE_PNG));
                            color.flush();
                            byteArrayOutputStream.flush();
                            byteArrayOutputStream.close();
                        }

                    }

                }
            }
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            wb.write(bos);
            bos.close();
            bos.flush();
            bytes = bos.toByteArray();
        }catch (Exception e){
            e.printStackTrace();
        }
        return bytes;
    }

    private static Object getCellFormatValue(Cell cell,boolean flag){
        Object cellValue = null;
        if(cell!=null){
            //判断cell类型
            switch(cell.getCellTypeEnum()){
                case NUMERIC:{
                    //判断cell是否为日期格式
                    if(HSSFDateUtil.isCellDateFormatted(cell)){
                        cellValue = String.valueOf(cell.getDateCellValue().getTime());
                    }else{
                        if(flag){
                            cellValue = String.valueOf(cell.getNumericCellValue());
                        }else {
                            cellValue = String.valueOf((int)cell.getNumericCellValue());
                        }
                    }
                    break;
                }
                case STRING:{
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        }else{
            cellValue = "";
        }
        return cellValue;
    }
    private static String stringFormat(String s){
        if("编号".equals(s))
            return "itemNo";
        if("类别".equals(s))
            return "type";
        if("名称".equals(s))
            return "name";
        if("状态".equals(s))
            return "status";
        if("开始时间".equals(s))
            return "originStartTime";
        if("结束时间".equals(s))
            return "originEndTime";
        if("实际开始时间".equals(s))
            return "startTime";
        if("实际完成时间".equals(s))
            return "completedTime";
        if("逾期拟完成时间".equals(s))
            return "overdueTime";
        if("逾期比".equals(s))
            return "overdueRate";
        if("逾期次数".equals(s))
            return "overdueTimes";
        if("工时".equals(s))
            return "workHour";
        if("风险项".equals(s))
            return "risk";
        if("交付物".equals(s))
            return "achievement";
        if("标签".equals(s))
            return "tags";
        if("里程碑".equals(s))
            return "milepost";
        if("负责人".equals(s))
            return "manager";
        if("成员".equals(s))
            return "member";
        if("唯一标识".equals(s))
            return "id";
        return s;
    }


}
